create PROCEDURE lookupRentProjection
(
    @Apn varchar(50)
)

AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @f2 DECIMAL(18, 4), @k2 DECIMAL(18, 4), @l2 DECIMAL(18, 4)
	DECLARE @m2 DECIMAL(18, 4), @n2 DECIMAL(18, 4), @p2 DECIMAL(18, 4)
	
	SELECT @f2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentF2'
	
	SELECT @k2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentK2'
	
	SELECT @l2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentL2'
	
	SELECT @m2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentM2'
	
	SELECT @n2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentN2'
	
	SELECT @p2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentP2'
		
	SELECT RentProjection.*, ua.Fname + ' ' + ua.LName as InvestorName,
	RentProjection.ClosingCost + RentProjection.PurchasePrice AS PurchaseTotal,
	RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost AS TotalAcquisition,
	CONVERT(DECIMAL(18, 2), RentProjection.PurchasePrice * 0.35 * @k2 / 12) AS TaxesMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @l2) AS AnnualVacancyLossMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @m2) AS MaintenanceMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @n2) AS ManagementMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @p2) AS InsuranceMonth,
	
	CONVERT(DECIMAL(18, 2), RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth) AS MonthlyExpense,
	
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) AS MonthlyCashFlowProfit,
	
	CONVERT(DECIMAL(18, 2), (RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) * 12) AS AnnualCashFlowProfit,
	
	CONVERT(DECIMAL(18, 2), CASE WHEN (RentProjection.RentMonth = 0) or (RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost = 0) THEN 0
	ELSE 
	(RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) * 12 /
	(RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost) * 100 
	END) AS CashFlowProfitVsTotalAcquisition,
	
	(select max(PropertyId) from LvReo_MasterList where LvReo_MasterList.Apn = RentProjection.Apn) as PropertyId,
	case when exists(select 1 from RentProjectionUnit where RentProjectionUnit.Apn = RentProjection.Apn) then '+' else '' end as HasUnit
	
	FROM RentProjection LEFT JOIN UserAccounts AS ua ON RentProjection.InvestorId = ua.UserId
	WHERE (RentProjection.Apn = @Apn)
END
go
ALTER PROCEDURE [dbo].[getRentProjection]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '',
        
    @UserId INT = NULL
)

AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @f2 DECIMAL(18, 4), @k2 DECIMAL(18, 4), @l2 DECIMAL(18, 4)
	DECLARE @m2 DECIMAL(18, 4), @n2 DECIMAL(18, 4), @p2 DECIMAL(18, 4)
	
	SELECT @f2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentF2'
	
	SELECT @k2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentK2'
	
	SELECT @l2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentL2'
	
	SELECT @m2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentM2'
	
	SELECT @n2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentN2'
	
	SELECT @p2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentP2'
		
	SELECT RentProjection.*, ua.Fname + ' ' + ua.LName as InvestorName,
	RentProjection.ClosingCost + RentProjection.PurchasePrice AS PurchaseTotal,
	RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost AS TotalAcquisition,
	CONVERT(DECIMAL(18, 2), RentProjection.PurchasePrice * 0.35 * @k2 / 12) AS TaxesMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @l2) AS AnnualVacancyLossMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @m2) AS MaintenanceMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @n2) AS ManagementMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @p2) AS InsuranceMonth,
	
	CONVERT(DECIMAL(18, 2), RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth) AS MonthlyExpense,
	
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) AS MonthlyCashFlowProfit,
	
	CONVERT(DECIMAL(18, 2), (RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) * 12) AS AnnualCashFlowProfit,
	
	CONVERT(DECIMAL(18, 2), CASE WHEN (RentProjection.RentMonth = 0) or (RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost = 0) THEN 0
	ELSE 
	(RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) * 12 /
	(RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost) * 100 
	END) AS CashFlowProfitVsTotalAcquisition,
	
	(select max(PropertyId) from LvReo_MasterList where LvReo_MasterList.Apn = RentProjection.Apn) as PropertyId,
	
	RentProjectionUnit.Bathrooms, RentProjectionUnit.Bedrooms,
	RentProjectionUnit.Rehab, RentProjectionUnit.SquareFootage, RentProjectionUnit.UnitNumber,
	RentProjectionUnit.RentProjectionUnitId
	
	FROM RentProjection LEFT JOIN UserAccounts AS ua ON RentProjection.InvestorId = ua.UserId
			left join RentProjectionUnit on RentProjection.Apn = RentProjectionUnit.Apn
	WHERE (RentProjection.Apn LIKE '%' + @Apn + '%')
		AND (ISNULL(PropertyLocation, '') LIKE '%' + @PropLocation + '%')
		AND (ISNULL(ZipCode, '') LIKE '%' + @Zip + '%') 
		AND (ISNULL(InvestorId, -1) = ISNULL(@UserId, ISNULL(InvestorId, -1)))
END
go
ALTER PROCEDURE [dbo].[updateRentProjection]
(
    @Apn VARCHAR(50),
	@PropertyLocation VARCHAR(400),
	@InvestorId INT,
	@ZipCode VARCHAR(20),
	@PurchasePrice MONEY,
	@ClosingCost MONEY,
	@RehabCost MONEY,
	@RentMonth MONEY,
	@HOAMonth MONEY,
	
	@RenterName VARCHAR(100) = NULL,
	@RenterPhone VARCHAR(50) = NULL,
	@DateRentedFrom DATETIME = NULL,
	@DateRentedTo DATETIME = NULL,
	@ManagementCompany VARCHAR(100) = NULL,
	@Contact VARCHAR(100) = NULL,
	@CompanyPhone VARCHAR(50) = NULL,
	@CompanyAddress VARCHAR(400) = NULL,
	
	@RentProjectionUnitId int = null,
	@UnitNumber varchar(50) = null,
	@SquareFootage float = null,
	@Bedrooms int = null,
	@Bathrooms varchar(50) = null,
	@Rehab money = null
)

AS
BEGIN
	SET NOCOUNT ON
	set xact_abort on
	
	begin transaction
	
		UPDATE RentProjection
		SET PropertyLocation = @PropertyLocation, InvestorId = @InvestorId, ZipCode = @ZipCode, 
			PurchasePrice = @PurchasePrice, ClosingCost = @ClosingCost, RehabCost = @RehabCost, 
			RentMonth = @RentMonth, HOAMonth = @HOAMonth,
			RenterName = @RenterName, RenterPhone = @RenterPhone, DateRentedFrom = @DateRentedFrom, DateRentedTo = @DateRentedTo, 
			ManagementCompany = @ManagementCompany, Contact = @Contact, CompanyPhone = @CompanyPhone, CompanyAddress = @CompanyAddress
		WHERE (Apn = @Apn)
		
		if (@RentProjectionUnitId is null)
			insert into RentProjectionUnit
			(Apn, UnitNumber, SquareFootage, Bedrooms, Bathrooms, Rehab)
			values (@Apn, @UnitNumber, @SquareFootage, @Bedrooms, @Bathrooms, @Rehab)
		else
			update RentProjectionUnit
			set UnitNumber = @UnitNumber, SquareFootage = @SquareFootage, Bedrooms = @Bedrooms, Bathrooms = @Bathrooms, Rehab = @Rehab
			where (RentProjectionUnitId = @RentProjectionUnitId)			
			
	commit transaction
END
go
ALTER PROCEDURE [dbo].[deleteRentProjection]
(
    @Apn VARCHAR(50),
    @RentProjectionUnitId int = null
)

AS
BEGIN
	SET NOCOUNT ON
	set xact_abort on
	
	begin transaction
		if @RentProjectionUnitId is not null
			delete from RentProjectionUnit where RentProjectionUnitId = @RentProjectionUnitId

		if not exists(select 1 from RentProjectionUnit where Apn = @Apn)
			DELETE 
			FROM RentProjection
			WHERE Apn = @Apn
	commit transaction
END
go